package autotest.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcDataUtil {

	public static void main(String[] args) {
		Connection conn = JdbcDataUtil.getConn(
				"jdbc:mysql://101.200.167.51:3306/api?useUnicode=true&characterEncoding=utf8",
				"testfan", "Testfan001");
		try {
			Object[][] data = JdbcDataUtil.getData(conn, "select id,nickname,mobile from t_user");
			System.out.println(data);
		} finally {
			JdbcDataUtil.closeConn(conn);
		}
	}

	public static Connection getConn(String url, String username, String password) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConn(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Object[][] getData(Connection conn, String query) {
		Object[][] data = null;

		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int column = meta.getColumnCount();
			List<Object[]> list = new ArrayList<Object[]>();
			while (rs.next()) {
				Object[] oa = new Object[column];
				for (int i = 1; i <= column; i++) {
					oa[i - 1] = rs.getObject(i);
				}
				list.add(oa);
			}
			if (list.size() > 0) {
				data = new Object[list.size()][column];
				for (int i = 0; i < list.size(); i++) {
					data[i] = list.get(i);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return data;
	}

	public static List<Map<String, Object>> executeQuery(Connection conn, String sql,
			String[] columns) {
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> dataList = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				for (String column : columns) {
					// String value = rs.getString(column);
					// row.put(column, value);
					row.put(column, rs.getObject(column));
				}
				dataList.add(row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return dataList;
	}

	// executeUpdate方法可以执行新增、更新、删除三种sql语句
	public static int executeUpdate(Connection conn, String sql) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			int updateCount = stmt.getUpdateCount();
			return updateCount;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
}
